﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Xml;
using GoShopping.BLL.SysInfo;

namespace GoShopping.Web.SystemManager
{
    public partial class AreaMaintain : System.Web.UI.Page
    {
        static string path = AppDomain.CurrentDomain.BaseDirectory;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Label1.Visible = false;
            }
        }
        private void GetTreeView()
        {
            TreeView tv = Master.MasterTreeView;
            SetControlCommon.GetPrdtTreeView(tv, Server.MapPath("~/ProductManager/Navigation.xml"), "sys");
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            //DataSet ds = GetGVData();
            string last = File1.FileName.Split('.').Last<string>();
            if (last != "mdb")
            {
                Label1.Visible = true;
                Label1.Text = "文件类型错误!";
                return;
            }
            Label1.Visible = false;
            File1.SaveAs(path + "1.mdb");
            GetCityArea(-1, "province", "-1");
            Label1.Visible = true;
            Label1.Text = "数据导入成功!";
        }
        /// <summary>
        /// 把数据插入到Sql Server中
        /// </summary>
        /// <param name="dt">保存数据的DataTable</param>
        /// <param name="tableName">要插入到Sql Server中的表名</param>
        /// <param name="pareIDsql">保存父类ID</param>
        private void InsertToSqlServer(DataTable dt, string tableName, object pareIDsql)
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["GoShoppingConnectionString"].ConnectionString;
            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connStr);
            System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
            sqlCmd.Connection = sqlConn;
            sqlConn.Open();
            switch (tableName)
            {
                case "province":
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //在批量操作中,参数名不能重名,故用@id+i.ToString()来区分变量名
                            sqlCmd.CommandText = "Insert INTo province(Name) Values('" + dt.Rows[i]["province"] + "');select @id" + i.ToString() + "=@@identity";
                            sqlCmd.Parameters.Add("@id"+i.ToString(), SqlDbType.Int).Direction = ParameterDirection.Output;
                            sqlCmd.ExecuteNonQuery();
                            object pareID = sqlCmd.Parameters["@id"+i.ToString()].Value;
                            GetCityArea(Convert.ToInt32(dt.Rows[i]["provinceID"]), "city", pareID);
                        }
                        sqlConn.Close();
                    }; break;
                case "city":
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //pareIDsql保证了在Sql Server中插入City的子类型后返回到这个循环时,仍然是City在Sql Server中的父类ID
                            sqlCmd.CommandText = "Insert INTO city(Name,provinceID) Values('" + dt.Rows[i]["city"] + "'," + pareIDsql.ToString() + ");select @id" + i.ToString() + "=@@identity";
                            sqlCmd.Parameters.Add("@id" + i.ToString(), SqlDbType.Int).Direction = ParameterDirection.Output;
                            sqlCmd.ExecuteNonQuery();
                            object pareID = sqlCmd.Parameters["@id" + i.ToString()].Value;
                            //插入City的子类时,pareID做为子类的父类ID(Sql Server中),dt.Rows[i]["cityID"]是City在Access中的ID,用做查询条件找出相关子类
                            GetCityArea(Convert.ToInt32(dt.Rows[i]["cityID"]), "area", pareID.ToString());
                        }
                        sqlConn.Close();
                    } break;
                case "area":
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            sqlCmd.CommandText = "Insert INTO county(Name,cityID) Values('" + dt.Rows[i]["area"] + "'," + pareIDsql.ToString() + ")";
                            sqlCmd.ExecuteNonQuery();
                        }
                        sqlConn.Close();
                    } break;
            }

        }
        /// <summary>
        /// 从Access中获取省,市,县,区
        /// </summary>
        /// <param name="pareID">父类ID</param>
        /// <param name="tableName">查询的表名</param>
        /// <param name="pareIDSql">Sql Server中的父类ID</param>
        private void GetCityArea(int pareID, string tableName, object pareIDSql)
        {
            System.Data.OleDb.OleDbConnection accessConn = new System.Data.OleDb.OleDbConnection();
            accessConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "1.mdb";  //+ ";User ID=Admin;Database Password=";
            accessConn.Open();
            System.Data.OleDb.OleDbCommand accessCmd = new System.Data.OleDb.OleDbCommand();
            switch (tableName)
            {
                case "province":
                    {
                        accessCmd.CommandText = "Select * from Province";
                        accessCmd.Connection = accessConn;
                        System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
                        DataTable dtProvince = new DataTable("province");
                        accessDA.Fill(dtProvince);
                        accessConn.Close();
                        InsertToSqlServer(dtProvince, "province", "-1");
                    }; break;
                case "city":
                    {
                        accessCmd.CommandText = "Select cityID,city from city where father='" + pareID.ToString() + "'";
                        accessCmd.Connection = accessConn;
                        System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
                        DataTable dtCity = new DataTable("city");
                        accessDA.Fill(dtCity);
                        accessConn.Close();
                        InsertToSqlServer(dtCity, "city", pareIDSql);
                    }; break;
                case "area":
                    {
                        accessCmd.CommandText = "Select areaID,area from area where father='" + pareID.ToString() + "'";
                        accessCmd.Connection = accessConn;
                        System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
                        DataTable dtArea = new DataTable("area");
                        accessDA.Fill(dtArea);
                        accessConn.Close();
                        InsertToSqlServer(dtArea, "area", pareIDSql);
                    }; break;
            }
        }

        #region Try
        //private DataSet GetGVData()
        //{
        //    System.Data.OleDb.OleDbConnection accessConn = new System.Data.OleDb.OleDbConnection();
        //    accessConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File1.Value;  //+ ";User ID=Admin;Database Password=";
        //    accessConn.Open();
        //    System.Data.OleDb.OleDbCommand accessCmd = new System.Data.OleDb.OleDbCommand();
        //    accessCmd.CommandText = "Select * from Province";
        //    accessCmd.Connection = accessConn;
        //    System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);

        //    DataSet ds = new DataSet();
        //    accessDA.Fill(ds, "province");
        //    accessConn.Close();
        //    gv.DataSource = ds.Tables["province"];
        //    gv.DataBind();
        //    return ds;
        //}
        //void GetProvince()
        //{
        //    System.Data.OleDb.OleDbConnection accessConn = new System.Data.OleDb.OleDbConnection();
        //    accessConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File1.Value;  //+ ";User ID=Admin;Database Password=";
        //    accessConn.Open();
        //    System.Data.OleDb.OleDbCommand accessCmd = new System.Data.OleDb.OleDbCommand();

        //    accessCmd.CommandText = "Select * from Province";
        //    accessCmd.Connection = accessConn;
        //    System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
        //    DataTable dtProvince = new DataTable("province");
        //    accessDA.Fill(dtProvince);
        //    accessConn.Close();


        //    string connStr = @"Data Source=RAY\SQLEXPRESS;Initial Catalog=GoShopping;Persist Security Info=True;User ID=sa;Password=1984326z";
        //    System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connStr);
        //    System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
        //    sqlCmd.Connection = sqlConn;
        //    XmlDocument xml = new XmlDocument();
        //    XmlElement root = xml.CreateElement("Root");
        //    xml.AppendChild(root);
        //    XmlElement firstLayer = xml.CreateElement("province");
        //    root.AppendChild(firstLayer);

        //    for (int i = 0; i < dtProvince.Rows.Count; i++)
        //    {
        //        sqlConn.Open();
        //        sqlCmd.CommandText = "Insert INTO province(Name) Values('" + dtProvince.Rows[i]["province"] + "');select @id" + i.ToString() + "=@@identity";
        //        sqlCmd.Parameters.Add("@id"+i.ToString(), SqlDbType.Int).Direction = ParameterDirection.Output;
        //        sqlCmd.ExecuteNonQuery();
        //        sqlConn.Close();
        //        string pareID = sqlCmd.Parameters["@id"+i.ToString()].Value.ToString();
        //        XmlNode xn = xml.CreateNode(XmlNodeType.Element, dtProvince.Rows[i]["provinceID"].ToString(), "");
        //        xn.InnerText = pareID;
        //        firstLayer.AppendChild(xn);
        //    }
        //    xml.Save("temp.xml");


        //}
        //void GetCity()
        //{
        //    System.Data.OleDb.OleDbConnection accessConn = new System.Data.OleDb.OleDbConnection();
        //    accessConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File1.Value;  //+ ";User ID=Admin;Database Password=";
        //    accessConn.Open();
        //    System.Data.OleDb.OleDbCommand accessCmd = new System.Data.OleDb.OleDbCommand();

        //    accessCmd.CommandText = "Select cityID,city,father from city";
        //    accessCmd.Connection = accessConn;
        //    System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
        //    DataTable dtCity = new DataTable("city");
        //    accessDA.Fill(dtCity);
        //    accessConn.Close();
        //    XmlDocument xml = new XmlDocument();
        //    xml.Load("temp.xml");
        //    XmlElement root = xml.DocumentElement;
        //    XmlNode firstLayer = xml.CreateElement("city");
        //    root.AppendChild(firstLayer);

        //    string connStr = @"Data Source=RAY\SQLEXPRESS;Initial Catalog=GoShopping;Persist Security Info=True;User ID=sa;Password=1984326z";
        //    System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connStr);
        //    System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
        //    sqlCmd.Connection = sqlConn;
        //    sqlConn.Open();
        //    for (int i = 0; i < dtCity.Rows.Count; i++)
        //    {
        //        //string idsql = htProvinceIDinSql[dtCity.Rows[i]["father"]].ToString();
        //        sqlCmd.CommandText = "Insert INTO city(Name,provinceID) Values('" + dtCity.Rows[i]["city"] + "'," + dtCity.Rows[i]["father"] + ");select @id" + i.ToString() + "=@@identity";
        //        sqlCmd.Parameters.Add("@id" + i.ToString(), SqlDbType.Int).Direction = ParameterDirection.Output;
        //        sqlCmd.ExecuteNonQuery();
        //        sqlConn.Close();
        //        string curIDsql = sqlCmd.Parameters["@id" + i.ToString()].Value.ToString();
        //        XmlNode xn = xml.CreateNode(XmlNodeType.Element, dtCity.Rows[i]["cityID"].ToString(), "");
        //        xn.InnerText = curIDsql;
        //        xn.Attributes["father"].Value = dtCity.Rows[i]["father"].ToString();
        //        firstLayer.AppendChild(xn);
        //    }
        //    xml.Save("temp.xml");
        //}
        //void GetArea()
        //{
        //    System.Data.OleDb.OleDbConnection accessConn = new System.Data.OleDb.OleDbConnection();
        //    accessConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + File1.Value;  //+ ";User ID=Admin;Database Password=";
        //    accessConn.Open();
        //    System.Data.OleDb.OleDbCommand accessCmd = new System.Data.OleDb.OleDbCommand();

        //    accessCmd.CommandText = "Select areaID,area from area where father='" + pareID.ToString() + "'";
        //    accessCmd.Connection = accessConn;
        //    System.Data.OleDb.OleDbDataAdapter accessDA = new System.Data.OleDb.OleDbDataAdapter(accessCmd);
        //    DataTable dtArea = new DataTable("area");
        //    accessDA.Fill(dtArea);
        //    accessConn.Close();


        //    string connStr = @"Data Source=RAY\SQLEXPRESS;Initial Catalog=GoShopping;Persist Security Info=True;User ID=sa;Password=1984326z";
        //    System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connStr);
        //    System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
        //    sqlCmd.Connection = sqlConn;
        //    for (int i = 0; i < dt.Rows.Count; i++)
        //    {
        //        sqlConn.Open();
        //        sqlCmd.CommandText = "Insert INTO county(Name,cityID) Values('" + dt.Rows[i]["area"] + "'," + pareID.ToString() + ")";
        //        sqlCmd.ExecuteNonQuery();
        //        sqlConn.Close();
        //    }
        //}
        #endregion
    }
}
